﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace PlateNumber {
    public class PPP {
        public int Id { get; set; }
        public string EntryName { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
        public string PhoneNumber { get; set; }
        public bool IsDefault { get; set; }

        public static DataTable Load() {
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM PPPs", Program.ConnectionString);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static PPP Get(int id) {
            using (OleDbConnection conn = new OleDbConnection(Program.ConnectionString)) {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand("SELECT * FROM PPPs WHERE Id = @Id", conn);
                cmd.Parameters.AddWithValue("@Id", id);
                OleDbDataReader reader = cmd.ExecuteReader();
                reader.Read();

                return new PPP() {
                    Id = Int32.Parse(reader["Id"].ToString()),
                    EntryName = reader["EntryName"].ToString(),
                    UserName = reader["UserName"].ToString(),
                    Password = reader["Password"].ToString(),
                    PhoneNumber = reader["PhoneNumber"].ToString(),
                    IsDefault = Boolean.Parse(reader["IsDefault"].ToString())
                };
            }
        }

        public static PPP GetDefault() {
            using (OleDbConnection conn = new OleDbConnection(Program.ConnectionString)) {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand("SELECT * FROM PPPs WHERE IsDefault = TRUE", conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                reader.Read();

                return new PPP() {
                    Id = Int32.Parse(reader["Id"].ToString()),
                    EntryName = reader["EntryName"].ToString(),
                    UserName = reader["UserName"].ToString(),
                    Password = reader["Password"].ToString(),
                    PhoneNumber = reader["PhoneNumber"].ToString(),
                    IsDefault = Boolean.Parse(reader["IsDefault"].ToString())
                };
            }
        }

        public static void SetDefault(int id) {
            using (OleDbConnection conn = new OleDbConnection(Program.ConnectionString)) {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand("UPDATE PPPs SET IsDefault = FALSE", conn);
                cmd.ExecuteNonQuery();

                cmd = new OleDbCommand("UPDATE PPPs SET IsDefault = TRUE WHERE Id = @Id", conn);
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }

        public static void Save(PPP ppp) {
            using (OleDbConnection conn = new OleDbConnection(Program.ConnectionString)) {
                conn.Open();

                OleDbCommand cmd = null;

                if (ppp.Id == 0) {
                    // 添加
                    cmd = new OleDbCommand("INSERT INTO PPPs(EntryName, UserName, [Password], PhoneNumber, IsDefault) VALUES(@EntryName, @UserName, @Password, @PhoneNumber, @IsDefault)", conn);
                    cmd.Parameters.AddWithValue("@EntryName", ppp.EntryName);
                    cmd.Parameters.AddWithValue("@UserName", ppp.UserName);
                    cmd.Parameters.AddWithValue("@Password", ppp.Password);
                    cmd.Parameters.AddWithValue("@PhoneNumber", ppp.PhoneNumber);
                    cmd.Parameters.AddWithValue("@IsDefault", ppp.IsDefault);
                }
                else {
                    // 编辑
                    cmd = new OleDbCommand("UPDATE PPPs SET EntryName=@EntryName, UserName=@UserName, [Password]=@Password, PhoneNumber=@PhoneNumber, IsDefault=@IsDefault WHERE Id=@Id", conn);
                    cmd.Parameters.AddWithValue("@EntryName", ppp.EntryName);
                    cmd.Parameters.AddWithValue("@UserName", ppp.UserName);
                    cmd.Parameters.AddWithValue("@Password", ppp.Password);
                    cmd.Parameters.AddWithValue("@PhoneNumber", ppp.PhoneNumber);
                    cmd.Parameters.AddWithValue("@IsDefault", ppp.IsDefault);
                    cmd.Parameters.AddWithValue("@Id", ppp.Id);
                }

                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }

        public static void Delete(int id) {
            using (OleDbConnection conn = new OleDbConnection(Program.ConnectionString)) {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand("DELETE FROM PPPs WHERE Id = @Id", conn);
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }
    }
}
